﻿/*****************************************************************************/
/* Stored Procedure :: DeleteCase                                            */
/*****************************************************************************/
/* Copyright (C) 2008, Perot Systems Corporation. All right reserved.        */
/* Developer: Ed. Mueller (edward.mueller@ps.net)                            */
/* Started:  08/20/2008                                                      */
/*****************************************************************************/
CREATE PROCEDURE [CaseManagement].[DeleteCase]
@OID bigint
AS 

---------------------------------------------------------------------------------------------------
--						Liberty
---------------------------------------------------------------------------------------------------
--  Stored Procedure :: DeleteCase
--                                                                            
---------------------------------------------------------------------------------------------------

SET NOCOUNT ON

DECLARE @RetCode int

BEGIN TRANSACTION 

IF (EXISTS (SELECT * FROM dbo.Cases WHERE (OID = @OID)))
BEGIN
	DELETE FROM dbo.Injuries
	FROM dbo.Injuries i
	INNER JOIN dbo.Cases c ON c.OID = i.CaseID
	WHERE (c.OID = @OID)
	IF (@@ERROR <> 0) Goto ExitError

	DELETE FROM dbo.Visits
	FROM dbo.Visits v
	INNER JOIN dbo.Cases c ON c.OID = v.CaseID
	WHERE (c.OID = @OID)
	IF (@@ERROR <> 0) Goto ExitError

	DELETE FROM dbo.GrantEvents
	FROM dbo.GrantEvents ge
	INNER JOIN dbo.Grants g ON g.OID = ge.GrantID
	INNER JOIN dbo.Cases c ON c.OID = g.CaseID
	WHERE (c.OID = @OID)
	IF (@@ERROR <> 0) Goto ExitError
	
	DELETE FROM dbo.GrantNotes
	FROM dbo.GrantNotes gn
	INNER JOIN dbo.Grants g ON g.OID = gn.GrantID
	INNER JOIN dbo.Cases c ON c.OID = g.CaseID
	WHERE (c.OID = @OID)
	IF (@@ERROR <> 0) Goto ExitError

	DELETE FROM dbo.Grants
	FROM dbo.Grants g
	INNER JOIN dbo.Cases c ON c.OID = g.CaseID
	WHERE (c.OID = @OID)
	IF (@@ERROR <> 0) Goto ExitError

	DELETE FROM dbo.CaseNotes
	WHERE (CaseID = @OID)
	IF (@@ERROR <> 0) Goto ExitError
	
	DELETE FROM dbo.Cases
	WHERE (OID = @OID)
	IF (@@ERROR <> 0) Goto ExitError
END

-- Normal Exit
IF (@@TRANCOUNT > 0)
BEGIN
	COMMIT TRANSACTION
END
SET @RetCode = 0
GOTO endRoutine              

ExitError:
IF (@@TRANCOUNT > 1)
BEGIN
	COMMIT TRANSACTION
END
ELSE IF (@@TRANCOUNT = 1)
BEGIN
	ROLLBACK TRANSACTION
END
SET @RetCode = 1
GOTO endRoutine              

endRoutine:
RETURN (@RetCode)
GO
